package br.com.portalecbahia.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper{

	private static final String DATABASE_NAME = "portalecbahia-apk-db";
	private static final int DATABASE_VERSION = 5;
	
	private static final String DATABASE_CREATE_NEWS = "CREATE TABLE tbl_news (" +
			"ID INTEGER NOT NULL , " +
			"position INTEGER NOT NULL  PRIMARY KEY,"+
			"title VARCHAR, " +
			"urlImage VARCHAR, " +
			"date DATETIME, " +
			"content VARCHAR, " +
			"urlWebSite VARCHAR);";
	
	private static final String DATABASE_CREATE_MEDIA = "CREATE TABLE tbl_media (" +
			"ID INTEGER PRIMARY KEY  NOT NULL , " +
			"title VARCHAR, " +
			"urlImage VARCHAR, " +
			"time VARCHAR, " +
			"date DATETIME, " +
			"urlMedia VARCHAR, " +
			"typeID INTEGER);";
	
	

	private static final String DATABASE_CREATE_MATCH = "CREATE TABLE tbl_match (" +
			"ID INTEGER PRIMARY KEY  NOT NULL , " +
		   "championshipName VARCHAR, " +
		   "stadimName VARCHAR, " +
		   "stadiumId VARCHAR, " +
		   "awayTeamName VARCHAR, "+
		   "awayTeamUrlImage VARCHAR, " + 
		   "homeTeamName VARCHAR, "  +
		   "homeTeamUrlImage VARCHAR, " +
		   "date DATETIME, " +
		   "dateUTC DATETIME, " +
		   "homeGoal INTEGER, " +
		   "awayGoal INTEGER, " +
		   "finished INTEGER," +
		   "homeTeamShortName VARCHAR,"+
		   "awayTeamShortName VARCHAR," +
		   "live INTEGER);";
	
	private static final String DATABASE_CREATE_ACTIONS = "CREATE TABLE tbl_actions (" +
			"ID INTEGER PRIMARY KEY  NOT NULL , " +
			"idMatch INTEGER NOT NULL,"+
			"description VARCHAR, " +
			"minute INTEGER, " +
			"type INTEGER," +
			"team VARCHAR);";
	
	private static final String DATABASE_CREATE_LAST_UPDATE_ACTION = "CREATE TABLE tbl_last_update_action (" +
			"ID INTEGER PRIMARY KEY  NOT NULL , " +
			"idMatch INTEGER NOT NULL,"+
			"date DATETIME);";
	
	private static final  String DATABASE_CREATE_RANKINGN  = "CREATE TABLE tbl_rankings (" +
			"ID INTEGER PRIMARY KEY  NOT NULL, " +
			"position INTEGER NOT NULL,"+
			"team VARCHAR, " +
			"points INTEGER NOT NULL,"+
			"games INTEGER NOT NULL,"+
			"wins INTEGER NOT NULL,"+
			"draws INTEGER NOT NULL,"+
			"losses INTEGER NOT NULL,"+
			"goalDifference INTEGER NOT NULL,"+
			"championshipId INTEGER NOT NULL);";
	
	private static final  String DATABASE_CREATE_STADIUM  = "CREATE TABLE tbl_stadium (" +
			"ID INTEGER PRIMARY KEY  NOT NULL, " +
			"name VARCHAR NOT NULL,"+
			"capacity VARCHAR, " +
			"urlImage VARCHAR NOT NULL,"+
			"dateInauguration VARCHAR NOT NULL,"+
			"latitude VARCHAR NOT NULL,"+
			"longitude VARCHAR NOT NULL," +
			"description VARCHAR NOT NULL);";
	
	private static final String DATABASE_CREATE_PLAYER = "CREATE TABLE tbl_player (" +
			"ID INTEGER PRIMARY KEY  NOT NULL , " +
			"position VARCHAR, " +
			"idPosition VARCHAR, " +
			"name VARCHAR,  "+
			"urlImage INTEGER, " +
			"description VARCHAR);";
	
	
	private static final String DATABASE_CREATE_NOTIFICATION = "CREATE TABLE tbl_notification (" +
			"ENABLE INTEGER NOT NULL );";

	private static final String DATABASE_DROP_NEWS = "DROP TABLE IF EXISTS tbl_news; ";
	private static final String DATABASE_DROP_MEDIA = "DROP TABLE IF EXISTS tbl_media; ";
	private static final String DATABASE_DROP_MATCH = "DROP TABLE IF EXISTS tbl_match; ";
	private static final String DATABASE_DROP_ACTIONS = "DROP TABLE IF EXISTS tbl_actions; ";
	private static final String DATABASE_DROP_LAST_UPDATE_ACTION = "DROP TABLE IF EXISTS tbl_last_update_action; ";	
	private static final String DATABASE_DROP_RANKING = "DROP TABLE IF EXISTS tbl_rankings; ";
	private static final String DATABASE_DROP_STADIUM = "DROP TABLE IF EXISTS tbl_stadium; ";
	private static final String DATABASE_DROP_PLAYER = "DROP TABLE IF EXISTS tbl_player; ";


	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase database) {
	
		database.execSQL(DATABASE_CREATE_NEWS);
		database.execSQL(DATABASE_CREATE_MEDIA);
		database.execSQL(DATABASE_CREATE_MATCH);
		database.execSQL(DATABASE_CREATE_ACTIONS);
		database.execSQL(DATABASE_CREATE_LAST_UPDATE_ACTION);
		database.execSQL(DATABASE_CREATE_RANKINGN);
		database.execSQL(DATABASE_CREATE_STADIUM);
		database.execSQL(DATABASE_CREATE_PLAYER);
		
		database.execSQL(DATABASE_CREATE_NOTIFICATION);
		database.execSQL(" INSERT INTO tbl_notification (ENABLE)  VALUES (1)");


		}

	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {

		database.execSQL(DATABASE_DROP_NEWS);  
		database.execSQL(DATABASE_DROP_MEDIA);  
		database.execSQL(DATABASE_DROP_MATCH);  
		database.execSQL(DATABASE_DROP_ACTIONS);  
		database.execSQL(DATABASE_DROP_LAST_UPDATE_ACTION);  
		database.execSQL(DATABASE_DROP_RANKING);  
		database.execSQL(DATABASE_DROP_STADIUM);  

		database.execSQL(DATABASE_DROP_PLAYER);  

	    this.onCreate(database);  
	}

}

